﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Shop.DAL
{
    public class SqlHelper
    {
        public static string connStr = ConfigurationManager.ConnectionStrings["shop"].ConnectionString;

        public static int ExecuteNonQuery(string sql, params SqlParameter[] sp)
        {
            using(SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand cmd=new SqlCommand(sql, conn);
                if (sp!= null)
                {
                    cmd.Parameters.AddRange(sp);
                }
                try
                {
                    conn.Open();
                    var res= cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return res;
                }
                catch
                {
                    throw;
                }
            }
        }
    
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] sp)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (sp!= null)
            {
                cmd.Parameters.AddRange(sp);
            }

            try
            {
                conn.Open();
                var reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return reader;
            }
            catch
            {
                throw;
            }
        }

        public static object ExecuteScalar(string sql, params SqlParameter[] sp)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (sp != null)
                {
                    cmd.Parameters.AddRange(sp);
                }
                try
                {
                    conn.Open();
                    var res = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return res;
                }
                catch
                {
                    throw;
                }
            }
        }
    }
}
